﻿using System;
using System.Data;
using System.Web.Services;
using ComicBookServices.ADO;

namespace ComicBookServices
{
    /// <summary>
    /// Summary description for ComicWebServices
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class ComicWebServices : WebService
    {

        [WebMethod]
        public bool Login(string username, string password)
        {
            string query = "SELECT * FROM [Users] WHERE (([Username] ='" + username + "') AND ([Password] ='" +
                           password + "'))";
            DataTable dt = AdoHelper.ExecuteDataTable(query);
            int rows = dt.Rows.Count;

            if (rows > 0)
            {
                return true;
            }
            return false;
        }
        
        [WebMethod]
        public ComicBook GetComicByTitle(string title)
        {
            DataTable data =
                AdoHelper.ExecuteDataTable(
                    String.Format(
                        "SELECT [Comics].[Title], [Comics].[DateOfRelease], [Publisher].[Name] AS [PublisherName], [Writer].[Name] AS [WriterName], [Artist].[Name] AS [ArtistName], [Category].[Name] AS [CategoryName], [Comics].[Price], [Comics].[Description] FROM [Comics], [Publisher], [Writer], [Artist], [Category] WHERE [Comics].[PublisherCode] = [Publisher].[Code] AND [Comics].[WriterCode] = [Writer].[Code] AND [Comics].[ArtistCode] = [Artist].[Code] AND [Comics].[CategoryCode] = [Category].[Code] AND [Comics].[Title] = '{0}'",
                        title));
            ComicBook book = null;
            if (data.Rows.Count > 0)
            {
                DataRow row = data.Rows[0];
                book = new ComicBook
                    {
                        Title = row["Title"].ToString(),
                        DateOfRelease = ((DateTime)row["DateOfRelease"]),
                        PublisherName = row["PublisherName"].ToString(),
                        WriterName = row["WriterName"].ToString(),
                        ArtistName = row["ArtistName"].ToString(),
                        CategoryName = row["CategoryName"].ToString(),
                        Price = row["Price"].ToString(),
                        Description = row["Description"].ToString()
                    };
            }

            return book;
        }

        [WebMethod]
        public ComicBook[] GetComicsByCategory(string category)
        {
            DataTable data =
                 AdoHelper.ExecuteDataTable(
                     String.Format(
                         "SELECT [Comics].[Title], [Comics].[DateOfRelease], [Publisher].[Name] AS [PublisherName], [Writer].[Name] AS [WriterName], [Artist].[Name] AS [ArtistName], [Category].[Name] AS [CategoryName], [Comics].[Price], [Comics].[Description] FROM [Comics], [Publisher], [Writer], [Artist], [Category] WHERE [Comics].[PublisherCode] = [Publisher].[Code] AND [Comics].[WriterCode] = [Writer].[Code] AND [Comics].[ArtistCode] = [Artist].[Code] AND [Comics].[CategoryCode] = [Category].[Code] AND [Category].[Name] = '{0}'",
                         category));
            ComicBook[] bookArray = new ComicBook[data.Rows.Count];
            for (int i = 0; i < bookArray.Length; i++)
            {
                bookArray[i] = new ComicBook
                {
                    Title = data.Rows[i]["Title"].ToString(),
                    DateOfRelease = ((DateTime)data.Rows[i]["DateOfRelease"]),
                    PublisherName = data.Rows[i]["PublisherName"].ToString(),
                    WriterName = data.Rows[i]["WriterName"].ToString(),
                    ArtistName = data.Rows[i]["ArtistName"].ToString(),
                    CategoryName = data.Rows[i]["CategoryName"].ToString(),
                    Price = data.Rows[i]["Price"].ToString(),
                    Description = data.Rows[i]["Description"].ToString()
                };
            }

            return bookArray;
        }

        [WebMethod]
        public ComicBook[] GetComicsByPublisher(string publisher)
        {
            DataTable data =
                AdoHelper.ExecuteDataTable(
                    String.Format(
                        "SELECT [Comics].[Title], [Comics].[DateOfRelease], [Publisher].[Name] AS [PublisherName], [Writer].[Name] AS [WriterName], [Artist].[Name] AS [ArtistName], [Category].[Name] AS [CategoryName], [Comics].[Price], [Comics].[Description] FROM [Comics], [Publisher], [Writer], [Artist], [Category] WHERE [Comics].[PublisherCode] = [Publisher].[Code] AND [Comics].[WriterCode] = [Writer].[Code] AND [Comics].[ArtistCode] = [Artist].[Code] AND [Comics].[CategoryCode] = [Category].[Code] AND [Publisher].[Name] = '{0}'",
                        publisher));
            ComicBook[] bookArray = new ComicBook[data.Rows.Count];
            for (int i = 0; i < bookArray.Length; i++)
            {
                bookArray[i] = new ComicBook
                {
                    Title = data.Rows[i]["Title"].ToString(),
                    DateOfRelease = ((DateTime)data.Rows[i]["DateOfRelease"]),
                    PublisherName = data.Rows[i]["PublisherName"].ToString(),
                    WriterName = data.Rows[i]["WriterName"].ToString(),
                    ArtistName = data.Rows[i]["ArtistName"].ToString(),
                    CategoryName = data.Rows[i]["CategoryName"].ToString(),
                    Price = data.Rows[i]["Price"].ToString(),
                    Description = data.Rows[i]["Description"].ToString()
                };
            }

            return bookArray;
        }
    }
}
